package com.hk.commons.poi.excel.write.handler;

import com.hk.commons.poi.excel.exception.ExcelWriteException;
import com.hk.commons.poi.excel.metadata.DataFormat;
import com.hk.commons.poi.excel.metadata.ExcelColumnInfo;
import com.hk.commons.poi.excel.metadata.StyleTitle;
import com.hk.commons.poi.excel.metadata.WriteWorkbook;
import com.hk.commons.poi.excel.style.CustomCellStyle;
import com.hk.commons.poi.excel.util.CellStyleBuilder;
import com.hk.commons.poi.excel.util.WriteExcelUtils;
import com.hk.commons.util.CollectionUtils;
import com.hk.commons.util.PropertyAccessorUtils;
import com.hk.commons.util.StringUtils;
import lombok.Getter;
import lombok.Setter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanWrapper;
import org.springframework.util.ClassUtils;

import java.io.OutputStream;
import java.time.temporal.Temporal;
import java.util.*;

/**
 * @author kevin
 */
public abstract class AbstractWriteableHandler implements WriteableHandler {

    protected Logger logger = LoggerFactory.getLogger(getClass());

    /**
     * 导出参数
     */
    protected WriteWorkbook currentWorkbook;

    /**
     * 工作表
     */
    protected Workbook workbook;

    protected CreationHelper creationHelper;

    private Drawing<?> drawing;

    /**
     *
     */
    private List<ExcelColumnInfo> columnInfoList;

    /**
     * <pre>
     * 缓存属性列样式
     * 每一个属性列都有相同的样式
     * </pre>
     */
    private final Map<String, CellStyle> cacheDataCellStyle = new HashMap<>();

    private static final Map<Class<?>, DataRowParse> dataRowParseMap;

    static {
        //这里的Key, 为Map类型，顺序的保证取决于数据的Map类型，如果data中的每行数据的Map是有序的，则导出的列也是有序的
        dataRowParseMap = Map.of(Map.class, new DataRowParse() {

            @Override
            public List<ExcelColumnInfo> buildExcelColumnInfo(WriteWorkbook currentWorkbook) {
                // map 类型
                if (CollectionUtils.isEmpty(currentWorkbook.getData())) {
                    return Collections.emptyList();
                }
                Map<?, ?> first = (Map<?, ?>) currentWorkbook.getData().getFirst();
                List<ExcelColumnInfo> columnInfos = new ArrayList<>();
                int index = 0;
                CustomCellStyle cellStyle = new CustomCellStyle();
                for (Object key : first.keySet()) {
                    String property = String.valueOf(key);
                    columnInfos.add(new ExcelColumnInfo()
                            .setTitle(new StyleTitle(currentWorkbook.getTitleRow(), index++, property, property))
                            .setStatistics(false)
                            .setCommentVisible(false)
                            .setDataStyle(cellStyle)
                    );
                }
                return columnInfos;
            }

            @Override
            public Object getPropertyValue(BeanWrapper beanWrapper, String propertyName) {
                return ((Map<?, ?>) beanWrapper.getWrappedInstance()).get(propertyName);
            }

            @Override
            public Class<?> getPropertyType(BeanWrapper beanWrapper, String propertyName) {
                Object value = ((Map<?, ?>) beanWrapper.getWrappedInstance()).get(propertyName);
                return Objects.isNull(value) ? Object.class : value.getClass();
            }
        }, Object.class, new DataRowParse() {

            @Override
            public List<ExcelColumnInfo> buildExcelColumnInfo(WriteWorkbook currentWorkbook) {
                return WriteExcelUtils.parse(currentWorkbook.getBeanClazz(), currentWorkbook.getTitleRow());
            }

            @Override
            public Object getPropertyValue(BeanWrapper beanWrapper, String propertyName) {
                return beanWrapper.getPropertyValue(propertyName);
            }

            @Override
            public Class<?> getPropertyType(BeanWrapper beanWrapper, String propertyName) {
                return beanWrapper.getPropertyType(propertyName);
            }
        });
    }

    interface DataRowParse {

        List<ExcelColumnInfo> buildExcelColumnInfo(WriteWorkbook currentWorkbook);

        Object getPropertyValue(BeanWrapper beanWrapper, String propertyName);

        Class<?> getPropertyType(BeanWrapper beanWrapper, String propertyName);
    }

    /**
     * 统计样式
     */
    @Getter
    @Setter
    private CustomCellStyle statisticsStyle;

    @Override
    public final void write(Workbook workbook, Iterable<WriteWorkbook> sheetBooks, OutputStream out) {
        this.workbook = workbook;
        try {
            creationHelper = this.workbook.getCreationHelper();
            init();
            int index = 0;
            for (WriteWorkbook sheetWorkbook : sheetBooks) {
                this.currentWorkbook = sheetWorkbook;
                this.columnInfoList = null;
                this.cacheDataCellStyle.clear();
                var sheet = this.workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetWorkbook.getSheetName()));
                drawing = sheet.createDrawingPatriarch();
                if (StringUtils.isNotEmpty(sheetWorkbook.getPassword())) {
                    sheet.protectSheet(sheetWorkbook.getPassword());
                }
                sheet.setDisplayGridlines(sheetWorkbook.isDisplayGridLines());
                writeSheet(sheet);
                this.workbook.setSheetHidden(index++, sheetWorkbook.isHidden());
            }
            this.workbook.write(out);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw new ExcelWriteException(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(this.workbook);
        }
    }

    /**
     * 写在入workbook之前初始化，如可以初始化样式
     */
    protected void init() {

    }

    protected abstract void writeSheet(Sheet sheet);

    /**
     * 合并单元格
     *
     * @param sheet    sheet
     * @param firstRow 从哪行开始
     * @param lastRow  从哪行结束
     * @param firstCol 从哪列开始
     * @param lastCol  从哪列结束
     * @return 合并单元格的索引
     */
    protected final int mergingCells(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * <pre>
     * 	获取批注内容
     * 	如果需要设置批注，请重写此方法
     * </pre>
     *
     * @param obj          对象
     * @param propertyName 对象属性
     * @param propertyType 对象属性类型
     * @return 需要设置的批注信息 ，如果返回值为 null || ""，不设置批注信息
     */
    protected String getCommentText(Object obj, String propertyName, Class<?> propertyType) {
        return null;
    }

    /**
     * 创建标题行
     *
     * @param sheet sheet
     */
    protected void createTitleRow(Sheet sheet) {
        var titleRow = sheet.createRow(currentWorkbook.getTitleRow());
        titleRow.setHeightInPoints(currentWorkbook.getTitleRowHeight());
        var columnInfoList = getColumnInfoList();
        columnInfoList.forEach(item -> {
            var title = item.getTitle();
            sheet.setColumnWidth(title.getColumn(), title.getColumnWidth());
            var cell = titleRow.createCell(title.getColumn(), CellType.STRING);
            setCellStyle(cell, title.getStyle(), null, null);
            setCellValue(cell, null, title.getValue());
        });
    }

    /**
     * 根据属性名称获取对应的单元格式
     *
     * @param propertyType propertyType
     * @return {@link CellType}
     */
    protected final CellType getCellType(Class<?> propertyType) {
        var cellType = CellType.STRING;
        if (Objects.nonNull(propertyType)) {
            if (ClassUtils.isAssignable(Number.class, propertyType)
                    || ClassUtils.isAssignable(Date.class, propertyType)
                    || ClassUtils.isAssignable(Temporal.class, propertyType)) {
                cellType = CellType.NUMERIC;
            } else if (ClassUtils.isAssignable(Boolean.class, propertyType)) {
                cellType = CellType.BOOLEAN;
            }
        }
        return cellType;
    }

    /**
     * 创建数据行
     *
     * @param sheet    sheet
     * @param dataList dataList
     */
    protected void createDataRows(Sheet sheet) {
        List<?> dataList = currentWorkbook.getData();
        if (CollectionUtils.isNotEmpty(dataList)) {
            var rowIndex = currentWorkbook.getDataStartRow();

            var statisticsFormula = new HashMap<Integer, String>();// 记录需要统计的列与公式
            var columnInfoList = getColumnInfoList();

            // 记录nested属性所在每个列、与属性名称的Map
            var columnNestedPropertyMap = new HashMap<Integer, String>();

            String nestedPropertyPrefix = null;// Nested属性前缀 ，如 users[].name ，则此值为 users
            for (var excelColumnInfo : columnInfoList) {
                var title = excelColumnInfo.getTitle();
                if (StringUtils.contains(title.getPropertyName(), WriteExcelUtils.NESTED_PROPERTY)) {
                    nestedPropertyPrefix = StringUtils.substringBefore(title.getPropertyName(),
                            WriteExcelUtils.NESTED_PROPERTY);
                    columnNestedPropertyMap.put(title.getColumn(),
                            StringUtils.substringAfter(title.getPropertyName(), WriteExcelUtils.NESTED_PROPERTY));
                }
            }
            BeanWrapper beanWrapper;
            Row row;
            Cell cell;
            DataRowParse dataRowParse = dataRowParseMap.getOrDefault(currentWorkbook.getBeanClazz(), dataRowParseMap.get(Object.class));
            for (var item : dataList) {
                beanWrapper = PropertyAccessorUtils.forBeanPropertyAccess(item);
                if (StringUtils.isEmpty(nestedPropertyPrefix)) {
                    row = createDataRow(sheet, item, rowIndex++);
                    for (var excelColumnInfo : columnInfoList) {
                        cell = createCell(row, item, excelColumnInfo, excelColumnInfo.getTitle().getPropertyName(), beanWrapper, dataRowParse);
                        statisticsFormula(excelColumnInfo, cell, statisticsFormula, StringUtils.COLON_SEPARATE);
                    }
                } else {
                    var collection = (Collection<?>) dataRowParse.getPropertyValue(beanWrapper, nestedPropertyPrefix);
                    if (org.springframework.util.CollectionUtils.isEmpty(collection)) {
                        row = createDataRow(sheet, item, rowIndex++);
                        for (var excelColumnInfo : columnInfoList) {
                            var title = excelColumnInfo.getTitle();
                            if (!StringUtils.contains(title.getPropertyName(), WriteExcelUtils.NESTED_PROPERTY)) {
                                cell = createCell(row, item, excelColumnInfo, title.getPropertyName(), beanWrapper, dataRowParse);
                                statisticsFormula(excelColumnInfo, cell, statisticsFormula, StringUtils.COLON_SEPARATE);
                            }
                        }
                    } else {
                        final int firstRow = rowIndex;
                        var margeCellColumnList = new HashSet<Integer>();
                        for (int index = 0, size = collection.size(); index < size; index++) {
                            row = createDataRow(sheet, item, rowIndex++);
                            for (var entry : columnNestedPropertyMap.entrySet()) {
                                var info = columnInfoList.stream()
                                        .filter(infoItem -> infoItem.getTitle().getColumn() == entry.getKey())
                                        .findFirst().orElseThrow(() -> new ExcelWriteException("No match value"));
                                final var nestedPropertyName = String.format(STR."%s\{WriteExcelUtils.NESTED_PROPERTY}%s", nestedPropertyPrefix, index, entry.getValue());
                                cell = createCell(row, item, info, nestedPropertyName, beanWrapper, dataRowParse);
                                statisticsFormula(info, cell, statisticsFormula, StringUtils.COLON_SEPARATE);
                            }
                            for (var excelColumnInfo : columnInfoList) {
                                var title = excelColumnInfo.getTitle();
                                if (!StringUtils.contains(title.getPropertyName(), WriteExcelUtils.NESTED_PROPERTY)) {
                                    margeCellColumnList.add(title.getColumn());
                                    cell = createCell(row, item, excelColumnInfo, title.getPropertyName(), beanWrapper, dataRowParse);
                                    if (index == 0) {//只需要统计第一条
                                        statisticsFormula(excelColumnInfo, cell, statisticsFormula, StringUtils.COMMA_SEPARATE);
                                    }
                                }
                            }
                        }
                        if (currentWorkbook.isMergeCell() && !margeCellColumnList.isEmpty()) {
                            for (var columnItem : margeCellColumnList) {
                                mergingCells(sheet, firstRow, rowIndex - 1, columnItem, columnItem);
                            }
                        }
                    }
                }
            }
            if (!statisticsFormula.isEmpty()) {
                buildStatisticsRow(statisticsFormula, sheet.createRow(rowIndex), getStatisticsStyle());
            }
        }
    }

    private void statisticsFormula(ExcelColumnInfo excelColumnInfo, Cell cell, Map<Integer, String> statisticsFormula, String separate) {
        if (excelColumnInfo.isStatistics() && cell.getCellType() == CellType.NUMERIC && !DateUtil.isCellDateFormatted(cell)) {
            var columnIndex = cell.getColumnIndex();
            var formula = statisticsFormula.get(columnIndex);
            var cellAddress = cell.getAddress().formatAsString();
            statisticsFormula.put(columnIndex, StringUtils.isEmpty(formula) ? cellAddress + separate : formula + separate + cellAddress);
        }
    }

    /**
     * 创建单元格，设置样式与值
     *
     * @param row             row
     * @param obj             obj
     * @param excelColumnInfo excelColumnInfo
     * @param beanWrapper     beanWrapper
     * @param helper          helper
     * @param drawing         drawing
     */
    private Cell createCell(Row row, Object obj, ExcelColumnInfo excelColumnInfo,
                            String nestedPropertyName, BeanWrapper beanWrapper, DataRowParse dataRowParse) {
        var title = excelColumnInfo.getTitle();
        var propertyType = dataRowParse.getPropertyType(beanWrapper, nestedPropertyName);
        var cell = row.createCell(title.getColumn(), getCellType(propertyType));
        setCellComment(cell, getCommentText(obj, nestedPropertyName, propertyType),
                excelColumnInfo.getCommentAuthor(), excelColumnInfo.isCommentVisible());
        if (StringUtils.contains(nestedPropertyName, WriteExcelUtils.NESTED_PROPERTY)) {
            nestedPropertyName = StringUtils.substringAfterLast(nestedPropertyName, WriteExcelUtils.NESTED_PROPERTY);
        }
        setCellStyle(cell, excelColumnInfo.getDataStyle(), nestedPropertyName, propertyType);
        setCellValue(cell, nestedPropertyName, dataRowParse.getPropertyValue(beanWrapper, nestedPropertyName));
        return cell;
    }

    /**
     * 创建数据行并设置行样式
     *
     * @param sheet    工作表
     * @param obj      行数据
     * @param rowIndex 行号
     * @return 有样式的行，如果样式存在
     */
    private Row createDataRow(Sheet sheet, Object obj, int rowIndex) {
        var row = sheet.createRow(rowIndex);
        row.setHeightInPoints(currentWorkbook.getDataRowHeight());
        var rowStyle = getRowStyle(obj, row.getRowNum());
        if (Objects.nonNull(rowStyle)) {
            row.setRowStyle(rowStyle.toCellStyle(workbook, DataFormat.TEXT_FORMAT));
        }
        return row;
    }

    /**
     * @param statisticsFormula statisticsFormula
     * @param row               row
     * @param statisticsStyle   statisticsStyle
     */
    protected void buildStatisticsRow(Map<Integer, String> statisticsFormula, Row row, CustomCellStyle statisticsStyle) {
        var style = CellStyleBuilder.buildCellStyle(workbook, statisticsStyle, DataFormat.TEXT_FORMAT);
        for (var info : getColumnInfoList()) {
            var title = info.getTitle();
            if (statisticsFormula.containsKey(title.getColumn())) {
                var cell = row.createCell(title.getColumn(), CellType.NUMERIC);
                cell.setCellFormula(String.format("SUM(%s)", statisticsFormula.get(title.getColumn())));
                cell.setCellStyle(style);
            }
        }
    }

    /**
     * 获取数据行样式
     *
     * @param obj    要填充的对象
     * @param rowNum 当前行数
     */
    protected CustomCellStyle getRowStyle(Object obj, int rowNum) {
        return null;
    }

    /**
     * 设置单元格样式
     *
     * @param cell         单元格
     * @param style        样式
     * @param propertyName 单元格对应的属性名,如果为null,使用 propertyType
     * @param propertyType 单元格对应的属性类型,如果为null,使用 com.hk.commons.poi.excel.model.DataFormat#TEXT_FORMAT
     */
    protected void setCellStyle(Cell cell, CustomCellStyle style, String propertyName, Class<?> propertyType) {
        var cellStyle = cacheDataCellStyle.get(propertyName);
        if (Objects.isNull(cellStyle) && Objects.nonNull(style)) {
            cellStyle = style.toCellStyle(workbook, currentWorkbook.getValueFormat().getFormat(propertyName, propertyType));
            if (StringUtils.isNotEmpty(propertyName)) {
                cacheDataCellStyle.put(propertyName, cellStyle);
            }
        }
        setCellStyle(cell, cellStyle);
    }

    /**
     * CellStyle set
     *
     * @param cell  cell
     * @param style style
     */
    protected final void setCellStyle(Cell cell, CellStyle style) {
        cell.setCellStyle(style);
    }

    /**
     * 构建数据单元格，设置值与批注信息
     *
     * @param cell         cell
     * @param propertyName 属性名
     * @param value        要设置的单元格值
     * @param commentText  批注内容
     * @param drawing      drawing
     * @param helper       helper
     * @param author       批注作者
     * @param visible      批注可见性
     */
    protected void buildDataCell(Cell cell, String propertyName, Object value, String commentText, Drawing<?> drawing,
                                 String author, boolean visible) {
        setCellValue(cell, propertyName, value);
        setCellComment(cell, commentText, author, visible);
    }

    /**
     * 设置单元格注释
     *
     * @param helper      helper
     * @param cell        cell
     * @param commentText commentText
     */
    private void setCellComment(Cell cell, String commentText, String author,
                                boolean visible) {
        if (StringUtils.isNotEmpty(commentText)) {
            var clientAnchor = creationHelper.createClientAnchor();
            clientAnchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
            clientAnchor.setCol1(cell.getColumnIndex());
            clientAnchor.setCol2(cell.getColumnIndex() + 1);// 只会占用一个单元格的位置
            clientAnchor.setRow1(cell.getRowIndex());
            clientAnchor.setRow2(cell.getRowIndex() + 1);// 只会占用一个单元格的位置
            var comment = drawing.createCellComment(clientAnchor);
            comment.setAuthor(author);// 作者
            comment.setVisible(visible); // 是否可见 ，默认隐藏，需要鼠标移动到指定的区域才可见
            comment.setString(creationHelper.createRichTextString(commentText));
            cell.setCellComment(comment);
        }
    }

    private String toStringValue(String propertyName, Object value) {
        var clazz = value.getClass();
        var dataFormat = currentWorkbook.getValueFormat().getFormat(propertyName, clazz);
        return dataFormat.format(value);
    }

    /**
     * 设置单元格值
     *
     * @param cell         单元格
     * @param propertyName 属性名称
     * @param value        属性值
     */
    protected final void setCellValue(Cell cell, String propertyName, Object value) {
        cell.setCellValue(Objects.isNull(value) ? StringUtils.EMPTY : toStringValue(propertyName, value));
    }

    private List<ExcelColumnInfo> getColumnInfoList() {
        if (Objects.isNull(columnInfoList)) {
            columnInfoList = dataRowParseMap.getOrDefault(currentWorkbook.getBeanClazz(), dataRowParseMap.get(Object.class))
                    .buildExcelColumnInfo(currentWorkbook);
        }
        return columnInfoList;
    }

}
